/****** Object:  Stored Procedure dbo.CGO_GameEnrollmentGetRank    Script Date: Sunday, March 06, 2011 ******/
IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'dbo.CGO_GameEnrollmentGetRank')
                    AND OBJECTPROPERTY(id, N'IsProcedure') = 1 ) 
  DROP PROCEDURE dbo.CGO_GameEnrollmentGetRank
GO


/* -------------------------------------------------------------------------------------
/   GameEnrollmentGetList 
/  ------------------------------------------------------------------------------------- */
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.CGO_GameEnrollmentGetRank  
  @EnrollID INT,
  @UserID INT
AS  
  IF NOT EXISTS ( SELECT  1
                  FROM    dbo.CGO_GameEnrollment M
                          INNER JOIN dbo.CGO_GameEnrollmentStatus S ON M.EnrollStatusID = S.StatusID
                  WHERE   EnrollmentID = @EnrollID
                          AND UserID = @UserID
                          AND StatusCode = 'COMPLETED' )
    BEGIN
      RETURN
    END
  
  DECLARE @UserRank INT
  DECLARE @EnrollStatusID INT
  DECLARE @PortalID INT
  DECLARE @EventID INT
  DECLARE @GameID INT
  
  SELECT  @EnrollStatusID = StatusID
  FROM    dbo.CGO_GameEnrollmentStatus
  WHERE   StatusCode = 'COMPLETED'
  
  SELECT  @PortalID = PortalID ,
          @EventID = EventID ,
          @GameID = GameID
  FROM    dbo.CGO_GameEnrollment
  WHERE   EnrollmentID = @EnrollID
          AND UserID = @UserID
          AND EnrollStatusID = @EnrollStatusID
  
  SELECT  @UserRank = UserRank
  FROM    ( SELECT  ROW_NUMBER() OVER ( ORDER BY Score DESC, EndTime ASC ) AS UserRank ,
                    EnrollmentID ,
                    UserID
            FROM    dbo.CGO_GameEnrollment
            WHERE   PortalID = @PortalID
                    AND EventID = @EventID
                    AND GameID = @GameID
                    AND EnrollStatusID = @EnrollStatusID
          ) T
  WHERE   T.EnrollmentID = @EnrollID
          AND T.UserID = @UserID
          
  SELECT @UserRank          
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO